package cn.datawin.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class JDBCUtil {
	private static JDBCUtil jdbcDataSource = new JDBCUtil();
	private static Query query = new Query();
	private javax.sql.DataSource dataSource;
	static Log log = LogFactory.getLog(JDBCUtil.class);
	private Properties prop = null;
	
	private JDBCUtil() {
	}
	
	public static JDBCUtil connect(String resource) throws IOException{
		Resource r = new Resource("/postgresql.properties");
		Properties prop = PropertiesUtil.loadProperties(r);
		jdbcDataSource.setProp(prop);
		jdbcDataSource.init();
		return jdbcDataSource;
	}

	public static Connection getConnection(){
		return jdbcDataSource._getConnection();
	}
	
	private void init(){
		try {
			dataSource = (DataSource) Class.forName((String)prop.remove("dataSource")).newInstance();
			ReflectUtil.populate(dataSource, prop);
			Connection conn =  getConnection();
			DatabaseMetaData mdm = conn.getMetaData();
			log.info("connect database success: " + mdm.getDatabaseProductName() + mdm.getDatabaseProductVersion());
			conn.close();
		} catch (Exception e) {
			log.error("初始化 jdbc 数据池失败::"+ e);
		} 
	}
	
	public Connection _getConnection(){
		try {
			Connection conn =  dataSource.getConnection();
			//!Proxy.isProxyClass(conn.getClass()) ? new DebugConnection().bindConnection(conn) : conn;
			return conn;
		} catch (SQLException e) {
			log.error("getConnection error::"+ e);
		}
		return null;
	}

	public Properties getProp() {
		return prop;
	}

	public void setProp(Properties prop) {
		this.prop = prop;
	}
	
	public static int insert(String sql, Object... params) throws SQLException{
		return query.insert(getConnection(), sql, params);
	}
	
	public static Object QueryObject(String sql, Object... params) throws SQLException{
		return query.queryObject(getConnection(), sql, params);
	}
	
	public static List<Map<String, Object>> QueryObjects(String sql, Object... params) throws SQLException{
		return query.queryObjects(getConnection(), sql, params);
	}
	
	/*static class DebugConnection implements InvocationHandler {
		private Connection conn = null;

		*//**
		 * Returns the conn.
		 * 
		 * @return Connection
		 *//*
		public Connection bindConnection(Connection conn) {
			this.conn = conn;
			return (Connection) Proxy.newProxyInstance(conn.getClass()
					.getClassLoader(), conn.getClass().getInterfaces(), this);
		}

		@Override
		public Object invoke(Object proxy, Method m, Object[] args)	throws Throwable {
			String methodName = m.getName();
			if ("prepareStatement".equals(methodName)|| "prepareCall".equals(methodName)) {
				log.info("[SQL]>> " + args[0]);
			}
			try {
				return m.invoke(conn, args);
			} catch (InvocationTargetException e) {
				throw e.getTargetException();
			} 
		}
	}*/
	
	
	static class Query{
		
		public int insert(Connection conn, String sql, Object... params) throws SQLException{
			try{
				PreparedStatement statement =  conn.prepareStatement(sql);
				bindParams(statement, params);
				return statement.executeUpdate();
			}finally{
				close(conn);
			}
			
		}
		
		public ResultSet query(Connection conn, String sql, Object... params) throws SQLException{
			PreparedStatement statement =  conn.prepareStatement(sql);
			bindParams(statement, params);
			return statement.executeQuery();
		}
		
		public Object queryObject(Connection conn, String sql, Object... params) throws SQLException{
			return getObject(conn,query(conn, sql, params));
		} 
		
		public List<Map<String, Object>> queryObjects(Connection conn, String sql, Object... params) throws SQLException{
			return getObjects(conn,query(conn, sql, params));
		} 
		
		public void bindParams(PreparedStatement statement, Object... params) throws SQLException{
			int i=0;
			for(Object obj: params){
				statement.setObject(++i, obj);
			}
		}
		
		protected Object getObject(Connection conn,ResultSet res) throws SQLException {
			try {
				if (res.next()) {
					return res.getObject(1);
				}
			} finally {
				close(res);
				close(conn);
			}
			return null;
		}
		protected List<Map<String, Object>> getObjects(Connection conn,ResultSet res) throws SQLException {
			try {
				List<Map<String, Object>> objects=new ArrayList<Map<String, Object>>();
				while (res.next()) {
					Map<String, Object> map=new HashMap<String, Object>();
					map.put("id",res.getObject("id").toString());
					map.put("url",res.getObject("href").toString());
					objects.add(map);
				}
				return objects;
			} finally {
				close(res);
				close(conn);
			}
		}
		
		private void close(ResultSet res) throws SQLException{
			if(res != null){
				res.close();
			}
		}
		
		private void close(Connection conn) throws SQLException{
			if(conn != null){
				conn.close();
			}
		}
		
	}
	
	
	

	public static void main(String[] args) throws IOException, SQLException {
		
//		import org.postgresql.Driver;
//		import com.mchange.v2.c3p0.ComboPooledDataSource;
//		Resource r = new Resource("/postgresql.properties");
//		Properties prop = PropertiesUtil.loadProperties(r);
		JDBCUtil.connect("/postgresql.properties");
		JDBCUtil.getConnection();
		long a = System.currentTimeMillis();
		Object obj = JDBCUtil.QueryObject("select href from url where id=5000005");
		System.out.println(obj);
		System.out.println("====="+(System.currentTimeMillis()-a));
//		for(int i=5000009;i<5000018;i++){
//			JDBCUtil.insert("insert into url(id, href) values(?,?)", i,"this is a new url "+i);
//		}
	}
	
}
